clear all
set more off, perm
set maxvar 120000
set varabbrev off
* ---------------------------------------------- *
global dir 	"`1'"
global Data 	$dir/Data
global Tables 	$dir/Tables
global Figures 	$dir/Figures
global Work 	$dir/Work
global Temp 	$dir/Temp
global Pseudo	$dir/Work/Temp
* ---------------------------------------------- *
* get market cap and adjustment factors from CRSP
import sas $Data/CRSP/dsf.sas7bdat, clear case(lower)

keep permco permno date shrout cfacshr prc 
keep if year(date)>1975 
replace  prc=abs(prc)
generate mcap=shrout*prc
/* ******************** */
* create unique id 
preserve
keep permco permno
duplicates drop
order permco permno
sort permco permno 
gen crspid=_n
tempfile id
save "`id'", replace
restore
/* ******************** */
* deal with weekends
tsset permno date
tsfill
foreach var of varlist permco shrout cfacshr mcap  {
	replace `var'=L1.`var' if `var'==.
}
/* ******************** */
gen statpers=date
format statpers %td

merge m:1 permco permno using "`id'"
drop if _merge==2
drop _merge
/* ******************** */
save $Work/dsf, replace
/* ******************** */
* GET PRICE TARGET DATA*
import sas using $Data/IBES/ptgsumu.sas7bdat, case(lower) clear
keep if (index(measure,"PTG")>0 & usfirm==1 & index(curr,"USD")>0) 
recast str cname

replace ticker=trim(ticker)
keep ticker statpers medptg   
compress

keep if medptg<.
save $Temp/medptg, replace
* ****************************************
* Get IBES forecast data
import sas $dir/Data/IBES/statsumu_epsus.sas7bdat, clear case(lower)
rename *, lower
compress
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 

* The following 4 fpi values have 0.04% of all forecasts available on IBES
*"A": Nine fiscal years ahead.
*"B": Ten fiscal years ahead.
*"C": Eleven fiscal years ahead.
*"D": Twelve fiscal years ahead.
 
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")
destring fpi, replace
* *************
keep if fpi<=9

* fix some of the missing data
keep statpers ticker fpi medest fpedats

greshape wide medest fpedats, i(statpers ticker) j(fpi)
rename medest0 LTG
drop fpedats0

* When one-year forecasts are missing --> use at least 3 quarterly forecasts to build one-year forecasts (affects 2,832 obs out of 2,246,066 obs with medest1!=.)
egen tmp=rowmean(medest6 medest7 medest8 medest9)
egen nbr=rownonmiss(medest6 medest7 medest8 medest9)
generate lst=fpedats9
replace  lst=fpedats8 if lst==.
format lst %td
replace fpedats1=lst   if medest1==. & nbr>=3 & lst<fpedats2  /*fpedats2 is forecast period for FY2 -- sanity check */
replace  medest1=4*tmp if medest1==. & nbr>=3 & lst<fpedats2  
drop nbr tmp lst  
drop *6 *7 *8 *9

* fill in missing dates for forecast horizons
forvalues x=2(1)5 {
	local j=`x'-1
	replace fpedats`x'=fpedats`j'+365  		if fpedats`x'==.
}
* winsorize LTG
gstats winsor LTG, cuts(1 99) replace

* interpolate LTG (affects 211,925 obs out of 1,379,431 obs)
rename LTG LTG_raw
bys ticker: ipolate LTG_raw statpers, gen(LTG)

forvalues x=6(1)7 {
	generate fpedats`x'=fpedats5+365*(`x'-5)
	format fpedats`x' %td
	generate medest`x'=medest5*(1+LTG/100)^(`x'-5)	if medest5>0 & LTG>=-100
}
* Get data on "medest" ready for interpolation 
preserve
keep ticker statpers LTG
merge 1:1 ticker statpers using $Temp/medptg
drop _merge
save $Temp/LTG_PTG, replace
restore
* *******************
drop LTG LTG_raw
greshape long medest fpedats, i(ticker statpers) j(fpi)

* one obs has all missing values -- creates duplicate observations for fpedats
drop if ticker=="MICT" & fpedats==.

* one firm has two forecasts made on 1/14/1999 for 12/31/2002
drop if ticker=="FCX"  & fpedats==mdy(12,31,2002) & mdy(1,14,1999)==statpers & fpi==5
* **************
* interpolate the data
egen id=group(ticker statpers)
gen fpdatem=mofd(fpedats)
format fpdatem %tm
* **** 
drop fpedats fpi
tsset id fpdatem
tsfill
* ****
sort id fpdatem 
replace statpers=L.statpers if missing(statpers) & id==id[_n-1] 
replace ticker=ticker[_n-1] if missing(ticker)   & id==id[_n-1]

by id: ipolate medest fpdatem, gen(imedest)

* create variable with forecast horizon (in months)
gen fpi=fpdatem-mofd(statpers)
keep if fpi>0 & fpi<=60

drop id medest
greshape wide imedest fpdatem, i(ticker statpers) j(fpi)
rename imedest* medest*

merge 1:1 ticker statpers using $Temp/LTG_PTG
drop _merge
order ticker statpers LTG medptg medest* fpd*
forvalues x=1(1)60 {
	generate medestr`x'=medest`x'
}
forvalues x=13(1)60 {
	local j=`x'-1
	replace medest`x'=medest`j'*(1+LTG/100)^(1/12) if medest`x'==. & medest`j'>=0 & LTG>=-100
	
}
save $Temp/ibes.dta, replace
* ****************************************************************
* merge with IBES data on most recent earnings and shares outstanding 
import sas $Data/IBES/actpsumu_epsus.sas7bdat, clear case(lower)
keep if index(measure,"EPS") & index(curcode,"USD") 

rename fy0a 	FY0A 
rename fy0edats FY0EDATS
keep ticker statpers FY0A shout FY0EDATS  

* make sure there is only one ticker/statpers observation 
duplicates tag ticker statpers,gen(dup)
tabulate dup
drop dup 
merge 1:1 ticker statpers using $Temp/ibes.dta
drop if _merge==1
drop _merge
save $Temp/ibes_act.dta, replace
* ****************************************************************
* merge with IBES split factors
import sas using $Data/IBES/adjsum.sas7bdat, case(lower) clear
keep ticker statpers adjspf 

merge 1:1 ticker statpers using $Temp/ibes_act.dta
drop _merge
* ************
* tsset to fill in missing values of adjspf (i.e. assume no splits when adjspf is missing)
egen id=group(ticker)
gen datem=mofd(statpers)
format datem %tm

tsset id datem
tsfill
replace adjspf=L.adjspf if adjspf==.
replace adjspf=. 	if adjspf==0

* statpers is missing when medest/LTG is missing
drop if statpers==.
drop id 
* ****************************************************************
* add permcos
merge m:1 ticker statpers using $Work/link_ibes_crsp2024, keepusing(comnam cname permco permno ticker statpers) 
keep if _merge==3  
drop _merge
* make sure there is only one observation per security/statpers
duplicates tag permco permno statpers, gen(dup)
tabulate dup
drop dup
* ****************************************
* get CRSP split-adjustment, shares outstanding and mkt cap data 
merge 1:1 permco permno statpers using $Work/dsf
keep if _merge==3
drop _merge

* use CRSP split-adjustment factor
foreach var of varlist shrout shout {
	replace  `var'=`var'*cfacshr 
}
foreach var of varlist med* FY0A prc {
	replace  `var'=`var'/cfacshr 
}
* ****************************************
* Berkshire Hathaway & Google have a unique situation, i.e. 2 versions of the same stock: one cheap and one expensive
* LTG may be missing for one or the other.  Fill in missing LTG as appropriate.  This adjustment affects 49 observations. 
bys permco statpers: egen mLTG=mean(LTG)
replace LTG=mLTG if (index(ticker,"GOOG") | index(ticker, "BKHT")) & LTG==.
drop mLTG

* check that i don't have duplicates
sort permco permno statpers datem
duplicates tag crspid statpers, gen(dup)
tabulate dup
drop dup

tsset permno datem
rename medptg ptg
forvalues t=12(12)84 {
	local x = `t'/12
	gen LTGYr`x'=F`t'.LTG
	gen ptgYr`x'=F`t'.ptg
}
order comnam cname permco permno ticker statpers date datem FY* mcap shrout shout adj* cfacshr LTG* prc ptg* medest* fp*
format date %td
* add data on quarterly EPS forecasts
merge 1:1 ticker statpers using $Work/read_ibes_qtly.dta
drop if _merge==2
drop _merge
foreach var of varlist *QTR*  {
	replace  `var'=`var'/cfacshr 
}
save $Work/XSibes.dta, replace
/* ********************************************************************* */
!find $Temp -type f -delete
